package com.b511.swpu.cementbackend.repository;

import com.b511.swpu.cementbackend.entity.BA_Well;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

// 对应表 BA_WELL
public interface BA_WellRepository extends JpaRepository<BA_Well, Long>, JpaSpecificationExecutor<BA_Well>{

    @Transactional
    BA_Well findByWellName(String wellName);

    @Query(value = "select distinct country from BA_WELL where cementcompany=:cementcompany",nativeQuery = true)
    List<String> findCountry(@Param("cementcompany") String cementcompany);

    @Query(value = "select distinct area from BA_WELL where cementcompany=:cementcompany and country=:country",nativeQuery = true)
    List<String> findArea(@Param("cementcompany") String cementcompany,@Param("country") String country);

    @Query(value = "select distinct block from BA_WELL where cementcompany=:cementcompany and country=:country and area=:area",nativeQuery = true)
    List<String> findBlock(@Param("cementcompany") String cementcompany,@Param("country") String country,@Param("area") String area);

    @Query(value = "select distinct oilfield from BA_WELL where cementcompany=:cementcompany and country=:country and area=:area and block=:block",nativeQuery = true)
    List<String> findOilField(@Param("cementcompany") String cementcompany,@Param("country") String country,@Param("area") String area,@Param("block") String block);

    @Query(value = "select distinct wellname from BA_WELL where cementcompany=:cementcompany and country=:country and area=:area and block=:block and oilfield=:oilfield",nativeQuery = true)
    List<String> findWellName(@Param("cementcompany") String cementcompany,@Param("country") String country,@Param("area") String area,@Param("block") String block,@Param("oilfield") String oilfield);

    @Query(value = "select name from sysm_datadict where dictcode like '001001__'",nativeQuery = true)
    List<String> findDistinctArea();

    @Query(value = "select name from sysm_datadict where dictcode like '0003__'",nativeQuery = true)
    List<String> findDistinctWellType();

    @Query(value = "select name from sysm_datadict where dictcode like '0012__'",nativeQuery = true)
    List<String> findCaseSize();

    @Query(value = "select name from sysm_datadict where dictcode like '0004__'",nativeQuery = true)
    List<String> findDistinctWellSpacing();

    @Transactional
    void deleteByWellName(String wellName);

    @Query(value = "select * from BA_WELL where cementcompany=:cementcompany",nativeQuery = true)
    List<BA_Well> findAllByCementCompany(@Param("cementcompany") String cementcompany);




}
